import pymysql

'''
调用函数需要提前指定数据库如些信息：
host = ''
port = 
user = ''
password = ''
db = ''
'''
host = '59.110.143.227'
port = 3306
user = 'root'
password = '1qaz!QAZ'
database = 'bsp2'
db = pymysql.connect(host=host, port=port, user=user, password=password, database=database, charset='utf8')


def insertdb(sql):
    '''插入数据'''
    cursor = db.cursor()

    try:
        cursor.execute(sql)
        db.commit()  # 编辑后，用commit()保存
    except Exception as _:
        db.rollback()  # 发生错误时回滚
    finally:
        cursor.close()


def deletedb(sql):
    '''删除数据'''
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()  # 编辑后，用commit()保存
    except Exception as error:
        print('delete data error!:', error)
        db.rollback()  # 发生错误时回滚
    finally:
        cursor.close()


def updatedb(sql):
    '''修改数据'''
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
    except Exception as error:
        print('update data error!:', error)
        db.rollback()
    finally:
        cursor.close()


def selectdb(sql):
    '''查询数据'''
    cursor = db.cursor()  # 以元组返回结果
    # cursor = db.cursor(cursor = pymysql.cursor.DictCursor)   #以字典格式返回查询的结果
    try:
        cursor.execute(sql)
        data = cursor.fetchall()
    except Exception as error:
        data = tuple()
        print('select data error!:', error)
    finally:
        cursor.close()
    return data
